[« Back to Index](../00%20-%20Index.md)

# Airports

## Airport Classification

### By Passenger Volume

| Category | Annual Passengers | Examples |
|----------|-------------------|----------|
| Mega-hub | >70M | ATL, DXB, PEK |
| Major hub | 30M–70M | JFK, LHR, SIN |
| Mid-size | 10M–30M | MUC, BCN, SEA |
| Regional | 1M–10M | BRS, OPO, MSY |
| Small | <1M | Most regional airports |

### By Role

| Role | Description |
|------|-------------|
| Primary Hub | Airline's main base (ATL for Delta) |
| Secondary Hub | Secondary focus city (MIA for American) |
| Focus City | Significant but not hub-level (AUS for Southwest) |
| Destination | Served but not strategic |
| Alternate | Backup airport for diversions |

## Airport Data Model (`dim_airport`)

| Field | Type | Description | Example |
|-------|------|-------------|---------|
| `airport_key` | INTEGER (SK) | Surrogate key generated by warehouse | 1234 |
| `airport_code` | VARCHAR(3) | IATA 3-letter code | JFK |
| `airport_name` | VARCHAR(100) | Full name | John F Kennedy International |
| `city` | VARCHAR(50) | City name | New York |
| `country` | VARCHAR(50) | Country name | United States |
| `latitude` | DECIMAL(9,6) | Latitude | 40.6413 |
| `longitude` | DECIMAL(9,6) | Longitude | -73.7781 |
| `timezone` | VARCHAR(50) | IANA timezone string | America/New_York |
| `elevation_ft` | INTEGER | Elevation above sea level | 13 |

## Airport Codes Used in This Project

### Major Hubs Tracked

| Code | Airport | City | Country | Role |
|------|---------|------|---------|------|
| ATL | Hartsfield-Jackson | Atlanta | US | Delta hub |
| DXB | Dubai International | Dubai | UAE | Emirates hub |
| LAX | Los Angeles | Los Angeles | US | Multi-airline |
| LHR | London Heathrow | London | UK | British Airways hub |
| HND | Haneda | Tokyo | Japan | JAL/ANA hub |
| CDG | Charles de Gaulle | Paris | France | Air France hub |
| FRA | Frankfurt | Frankfurt | Germany | Lufthansa hub |
| SIN | Changi | Singapore | Singapore | Singapore Airlines hub |
| JFK | Kennedy | New York | US | JetBlue hub |
| ORD | O'Hare | Chicago | US | United hub |

## Airport Runway Configuration Impact

Flight delays correlate with runway count and layout:

| Configuration | Impact |
|--------------|--------|
| 4+ parallel runways | Best capacity, least delays |
| 2 crossing runways | Reduced capacity in crosswind |
| Single runway | Severe capacity limits (e.g., LGW) |
| Mountainous terrain | Approach restrictions, turbulence |

## Airport Weather Sensitivity

| Airport | Primary Weather Risk |
|---------|---------------------|
| ATL | Thunderstorms (summer) |
| ORD | Snow + wind (winter) |
| SFO | Fog (low visibility, year-round) |
| LHR | Fog + wind (year-round) |
| DXB | Sandstorms, extreme heat |
| DEN | Snow + high-density altitude |

This data feeds into the weather impact Gold table.

## Airport Capacity Metrics (Useful KPIs)

| Metric | Definition |
|--------|------------|
| **ATMs/hour** | Air Transport Movements per hour (capacity) |
| **Delay per operation** | Average delay per arrival/departure |
| **Cancellation rate** | % of flights cancelled at this airport |
| **Diversion count** | Flights diverted to this airport |
| **Delay hotspot** | Airport with delay > national avg |

```sql
-- Example: Find airports with above-average delays
SELECT
    dim_airport.airport_code,
    dim_airport.airport_name,
    AVG(fact_flights.delay_minutes) AS avg_delay
FROM fact_flights
JOIN dim_airport ON fact_flights.departure_airport_key = dim_airport.airport_key
GROUP BY dim_airport.airport_code, dim_airport.airport_name
HAVING AVG(fact_flights.delay_minutes) > (
    SELECT AVG(delay_minutes) FROM fact_flights
)
ORDER BY avg_delay DESC;
```

## Great-Circle Distance Calculation

For route efficiency analysis, we calculate distance between airports:

```python
import math


def haversine(lat1, lon1, lat2, lon2):
    """Calculate great-circle distance between two airports in km."""
    R = 6371  # Earth radius in km
    phi1 = math.radians(lat1)
    phi2 = math.radians(lat2)
    delta_phi = math.radians(lat2 - lat1)
    delta_lambda = math.radians(lon2 - lon1)

    a = (
        math.sin(delta_phi / 2) ** 2
        + math.cos(phi1) * math.cos(phi2) * math.sin(delta_lambda / 2) ** 2
    )
    c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
    return R * c


# Example: JFK → LHR
distance = haversine(40.6413, -73.7781, 51.4700, -0.4543)
# ≈ 5,541 km
```

## Airport Spatial Analysis

For the Gold layer's airport dashboard:
- Delay **heatmap**: Plot airports by lat/lon, color by avg delay
- **Traffic distribution**: Bubble chart by flight count
- **Regional patterns**: Group airports by continent/timezone

```python
# Polars example: Compute per-airport delay statistics
import polars as pl

airport_delays = (
    pl.scan_parquet("silver/flights/*.parquet")
    .group_by("departure_airport")
    .agg(
        [
            pl.col("delay_minutes").mean().alias("avg_delay"),
            pl.col("delay_minutes").quantile(0.95).alias("p95_delay"),
            pl.len().alias("flight_count"),
            (pl.col("status") == "C").sum().alias("cancellations"),
        ]
    )
    .sort("avg_delay", descending=True)
    .collect()
)
```

## Airport Reference Dataset

The `airports` ingestion module pulls from OurAirports/OpenFlights:
- ~9,300 airports worldwide
- Filter to commercial airports only (type: large_airport, medium_airport)
- Store full metadata in Hugging Face

```python
# Airports ingestion: Filter to relevant airports
airports_df = pl.read_csv("airports.dat")
commercial = airports_df.filter(pl.col("type").is_in(["large_airport", "medium_airport"]))
```

## Airport In SCD Tracking

Airport details change rarely but need tracking:
- **Type 1 SCD**: Overwrite on change (name change, ICAO code)
- **Type 2 SCD**: Track history (runway additions, category upgrades)

For this project: **Type 1 SCD** is sufficient (low change rate).

---
[« Back to Index](../00%20-%20Index.md)